The Data Journey: Messy to Meaningful¶

Phase 1¶

Understanding Domain :¶

Retail data captures everyday transactions—what customers buy, when they buy, and how much they spend. It’s rich with patterns that help businesses understand customer behavior, demand trends, and revenue drivers.

In real-world systems, data doesn’t come neatly packaged for ML.¶

We’re simulating a retail analytics pipeline where:

  • File 1 (Orders Data) → Transaction-level data (what was bought, when, how much)
  • File 2 (Country Mapping - later phase) → Enrichment data

Objective of the Series¶

We’ll transform raw transactional data into:

  • Business insights (revenue, trends)
  • Machine Learning use-case (e.g., customer segmentation / revenue prediction)

Your dataset contains:

  • Customer behavior → CustomerID
  • Transactions → InvoiceNo, InvoiceDate
  • Products → StockCode, Description
  • Revenue drivers → Quantity, UnitPrice

Phase 1: Data Understanding & Cleaning¶

  • Load and explore dataset
  • Understand structure & missing values
  • Handle missing data (drop/fill based on goal)
  • Fix data types (dates, numeric, IDs)

Goal: Turn raw, messy data into a clean and usable dataset for analysis & ML.

Import Libraries¶

In [1]:
import pandas as pd

import warnings
warnings.filterwarnings("ignore")

Load Dataset¶

In [2]:
df = pd.read_csv('Online Retail.csv', encoding="latin1")
df
Out[2]:
CustomerID InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice
0 17850.0 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 6 2010-12-01 08:26:00 2.55
1 17850.0 536365 71053 WHITE METAL LANTERN 6 2010-12-01 08:26:00 3.39
2 17850.0 536365 84406B CREAM CUPID HEARTS COAT HANGER 8 2010-12-01 08:26:00 2.75
3 17850.0 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 6 2010-12-01 08:26:00 3.39
4 17850.0 536365 84029E RED WOOLLY HOTTIE WHITE HEART. 6 2010-12-01 08:26:00 3.39
... ... ... ... ... ... ... ...
541904 12680.0 581587 22613 PACK OF 20 SPACEBOY NAPKINS 12 2011-12-09 12:50:00 0.85
541905 12680.0 581587 22899 CHILDREN'S APRON DOLLY GIRL 6 2011-12-09 12:50:00 2.10
541906 12680.0 581587 23254 CHILDRENS CUTLERY DOLLY GIRL 4 2011-12-09 12:50:00 4.15
541907 12680.0 581587 23255 CHILDRENS CUTLERY CIRCUS PARADE 4 2011-12-09 12:50:00 4.15
541908 12680.0 581587 22138 BAKING SET 9 PIECE RETROSPOT 3 2011-12-09 12:50:00 4.95

541909 rows × 7 columns

Initial Exploration¶

In [3]:
df.shape
Out[3]:
(541909, 7)
In [4]:
df.head()
Out[4]:
CustomerID InvoiceNo StockCode Description Quantity InvoiceDate UnitPrice
0 17850.0 536365 85123A WHITE HANGING HEART T-LIGHT HOLDER 6 2010-12-01 08:26:00 2.55
1 17850.0 536365 71053 WHITE METAL LANTERN 6 2010-12-01 08:26:00 3.39
2 17850.0 536365 84406B CREAM CUPID HEARTS COAT HANGER 8 2010-12-01 08:26:00 2.75
3 17850.0 536365 84029G KNITTED UNION FLAG HOT WATER BOTTLE 6 2010-12-01 08:26:00 3.39
4 17850.0 536365 84029E RED WOOLLY HOTTIE WHITE HEART. 6 2010-12-01 08:26:00 3.39
In [5]:
df.columns
Out[5]:
Index(['CustomerID', 'InvoiceNo', 'StockCode', 'Description', 'Quantity',
       'InvoiceDate', 'UnitPrice'],
      dtype='object')

Basic Info (Data Types + Nulls)¶

In [6]:
df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 541909 entries, 0 to 541908
Data columns (total 7 columns):
 #   Column       Non-Null Count   Dtype  
---  ------       --------------   -----  
 0   CustomerID   406829 non-null  float64
 1   InvoiceNo    541909 non-null  object 
 2   StockCode    541909 non-null  object 
 3   Description  540455 non-null  object 
 4   Quantity     541909 non-null  int64  
 5   InvoiceDate  541909 non-null  object 
 6   UnitPrice    541909 non-null  float64
dtypes: float64(2), int64(1), object(4)
memory usage: 28.9+ MB

Quick Data Profiling¶

In [7]:
df.describe()
Out[7]:
CustomerID Quantity UnitPrice
count 406829.000000 541909.000000 541909.000000
mean 15287.690570 9.552250 4.611114
std 1713.600303 218.081158 96.759853
min 12346.000000 -80995.000000 -11062.060000
25% 13953.000000 1.000000 1.250000
50% 15152.000000 3.000000 2.080000
75% 16791.000000 10.000000 4.130000
max 18287.000000 80995.000000 38970.000000

Check Missing Values¶

In [8]:
df.isnull().sum()
Out[8]:
CustomerID     135080
InvoiceNo           0
StockCode           0
Description      1454
Quantity            0
InvoiceDate         0
UnitPrice           0
dtype: int64

The most critical column — CustomerID — has the highest missing values (135K+)

This is not just a data issue… it’s a business reality.

  • These could be guest checkouts
  • Or untracked customers
  • Or even data collection gaps

Think Before You Clean

At this point, instead of rushing to fix it, pause and ask:

❓ What are we trying to build?

Because your decision here will directly impact:

  • Analysis quality
  • Machine Learning feasibility

Handling Missing Values¶

CustomerID: Dropped nulls since it's essential for ML & customer-level analysis

Note: You won’t always drop this — it depends on your goal. For sales analysis, you might keep it.

Description: Filled with "Unknown Product" to retain data without losing rows

In [9]:
df = df.dropna(subset=["CustomerID"])
df["Description"] = df["Description"].fillna("Unknown Product")
In [10]:
df.shape
Out[10]:
(406829, 7)

Fix Data Types¶

In [11]:
df["InvoiceDate"] = pd.to_datetime(df["InvoiceDate"])
df["CustomerID"] = df["CustomerID"].astype(int)

Remove Invalid Transactions¶

In [12]:
# Remove negative or zero quantity (returns/cancellations)
df = df[df["Quantity"] > 0]

# Remove zero or negative prices
df = df[df["UnitPrice"] > 0]

Remove Duplicates¶

In [13]:
df = df.drop_duplicates()

Quick Sanity Check¶

In [14]:
df.describe()
Out[14]:
CustomerID Quantity InvoiceDate UnitPrice
count 392692.000000 392692.000000 392692 392692.000000
mean 15287.843865 13.119702 2011-07-10 19:13:07.771892480 3.125914
min 12346.000000 1.000000 2010-12-01 08:26:00 0.001000
25% 13955.000000 2.000000 2011-04-07 11:12:00 1.250000
50% 15150.000000 6.000000 2011-07-31 12:02:00 1.950000
75% 16791.000000 12.000000 2011-10-20 12:53:00 3.750000
max 18287.000000 80995.000000 2011-12-09 12:50:00 8142.750000
std 1713.539549 180.492832 NaN 22.241836

Conclusion¶

We started with messy, real-world data—and turned it into something structured, reliable, and ready to work with.

Clean data isn’t exciting… but it’s where 80% of real work happens.

Everything that follows—analysis, insights, ML—depends on this foundation.

Coming Next: Phase 2 (Next Week)¶

Now that the data is clean… what stories is it hiding?

We’ll move into:

  • Exploring patterns in customer behavior
  • Understanding sales trends over time
  • Asking the right questions from the data

Because clean data is just the beginning—insight is where things get interesting.